Azure Synapse SQL Pool Query Not Executing Options

Scenario 1 ANSI SQL-89:

Picture this scenario, you have just migrated your query from a legacy Data Warehouse platform. You are attempting to confirm the overall runtime, however, the query session has yet to complete compiling. Yet, you see a large elapse time for the query, yet no start time.

You proceed to check if it's a concurrency delay, however, the resource class has not been assigned to the query yet. You further attempt to confirm the wait query in the other tip, to see if it's a resource wait issue, however, no data will derive from the session since it has not even been compiled yet.

The actual issue may be that your query is using a legacy ANSI SQL-89 syntax. Try altering the query to a ANSI SQL-92 syntax. Please see the below example.

Before ANSI SQL-89:

After ANSI SQL-92:

Scenario 2 Nested Subqueries:

Picture another scenario, you have a query that has a nested subquery of 42 Nested subqueries as the below. You attempt to execute the query but get the following error message:

Msg 102043, Level 16, State 1, Line 1

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

As the above error alludes to, Synapse SQL Pool supports a maximum of 32 Nested subqueries.

Synapse SQL Pool has certain capacity limits, it's important to review those limits in the development or migration stages. Reference: Capacity limits for dedicated SQL pool - Azure Synapse Analytics | Microsoft Docs

As an alternative, we can consider a UNION if no duplicate records are required, UNION ALL if duplicates records are required or even an EXIST clause. While breaking down the query into smaller parts.

Please remember to test to confirm the behavior, which is always recommended.

Nested subquery example: